from . import rgs
from flask import render_template, redirect, request, url_for, flash
from flask_login import login_required, current_user
from ..models import Student, Course, Takes, Inputbox, Prerequisite, Bill, Professor, Section, Teaches, Openingconfig
import datetime
from .. import db
from sqlalchemy import func
from .forms import NewStudentForm, StudentForm, UpdateStudentForm, NewProfessorForm, ProfessorForm, UpdateProfessorForm
from datetime import date


@rgs.route('/registrar-home', methods=['GET', 'POST'])
@login_required
def registrar_home():
    # 这是管理员的主页
    # 本页面前端提供链接到
    # 1. rgs.registrar_maintain_student_information
    # 2. rgs.registrar_maintain_professor_information
    # 3. rgs.registrar_list_course_to_cancel
    # 本路由没有数据库处理

    today = datetime.datetime.today()
    name = current_user.get_name()
    greeting = name + "管理员，您好！现在是" + str(today.year) + "年" + str(today.month) + "月" + str(today.day) + "日，" + str(
        today.hour) + "时" + str(
        today.minute) + "分"
    return render_template('registrar/registrar_home.html', greeting=greeting)


@rgs.route('/registrar-maintain-student-information', methods=['GET', 'POST'])
@login_required
def registrar_maintain_student_information():
    # 本页面前端提供提供链接到添加学生和删除学生以及update学生
    # 1. rgs.registrar_add_student
    # 2. rgs.registrar_delete_student
    # 3. rgs.registrar_update_student
    # 本路由无数据库处理
    return render_template('registrar/registrar_maintain_student_information.html')


@rgs.route('/registrar-add-student', methods=['GET', 'POST'])
@login_required
def registrar_add_student():
    # 本页面实现添加学生
    # 需要管理员填写表单：
    form = NewStudentForm()
    if form.validate_on_submit():
        str_student_id = str(form.student_id.data)
        if not str_student_id.startswith("2"):
            flash('学号必须以数字2开头，请确认后重新添加！')
            return render_template('registrar/registrar_add_student.html', form=form)

        dummy_student = Student.query.filter(Student.student_id == form.student_id.data).first()
        if dummy_student is not None:
            flash('系统已存在该学生！请检查学号是否输入正确！')
            return render_template('registrar/registrar_add_student.html', form=form)

        if form.balance.data < 0:
            flash('请输入非负的初始余额！')
            return render_template('registrar/registrar_add_student.html', form=form)

        student = Student(student_id=form.student_id.data,
                          password=form.password.data,
                          student_name=form.student_name.data,
                          date_of_birth=datetime.datetime.strptime(form.date_of_birth.data, '%Y-%m-%d'),
                          social_security_number=form.social_security_number.data,
                          status=form.status.data,
                          graduation_date=datetime.datetime.strptime(form.graduation_date.data, '%Y-%m-%d'),
                          department=form.department.data,
                          balance=form.balance.data)
        db.session.add(student)
        db.session.commit()
        flash('成功添加学生')
        return redirect(url_for('rgs.registrar_maintain_student_information'))
    return render_template('registrar/registrar_add_student.html', form=form)


@rgs.route('/registrar-delete-student', methods=['GET', 'POST'])
@login_required
def registrar_delete_student():
    # 本路由实现删除学生
    # 需要管理员填写表单：
    form = StudentForm()
    if form.validate_on_submit():
        student = Student.query.filter(Student.student_id == form.student_id.data).first()
        if student is None:
            flash('系统不存在该学生')
            return render_template('registrar/registrar_delete_student.html', form=form)
        # 删除该学生
        db.session.delete(student)
        db.session.commit()

        # 这里可能要考虑其他表中该学生的信息会不会自动根据key更新，如果会，则不需要额外的操作

        flash('成功删除该学生')
        return redirect(url_for('rgs.registrar_maintain_student_information'))
    return render_template('registrar/registrar_delete_student.html', form=form)


@rgs.route('/registrar-update-student', methods=['GET', 'POST'])
@login_required
def registrar_update_student():
    # 本路由实现更新学生信息
    # 需要管理员填写表单：
    # 这里的表单只有id是必填项
    form = UpdateStudentForm()
    if form.validate_on_submit():
        student = Student.query.filter(Student.student_id == form.student_id.data).first()
        if student is None:
            flash('系统不存在该学生')
            return render_template('registrar/registrar_update_student.html', form=form)
        # 这里用if-else暴力搜索，可能会有一些问题
        if form.password.data != "":
            student.password = form.password.data
        if form.student_name.data != "":
            student.student_name = form.student_name.data
        if form.date_of_birth.data != "":
            student.date_of_birth = datetime.datetime.strptime(form.date_of_birth.data, '%Y-%m-%d')
        if form.social_security_number.data != "":
            student.social_security_number = form.social_security_number.data
        if form.status.data != "":
            student.status = form.status.data
        if form.graduation_date.data != "":
            student.graduation_date = datetime.datetime.strptime(form.graduation_date.data, '%Y-%m-%d')
        if form.department.data != "":
            student.department = form.department.data
        db.session.add(student)
        db.session.commit()
        flash('更新学生信息成功')
        return redirect(url_for('rgs.registrar_maintain_student_information'))
    return render_template('registrar/registrar_update_student.html', form=form)


# --------------------
# 管理教师信息
# --------------------

# 基本等同上面对学生的操作

@rgs.route('/registrar-maintain-professor-information', methods=['GET', 'POST'])
@login_required
def registrar_maintain_professor_information():
    # maintain_professor_information.html提供链接到添加，删除以及update教授
    return render_template('registrar/registrar_maintain_professor_information.html')


@rgs.route('/registrar-add-professor', methods=['GET', 'POST'])
@login_required
def registrar_add_professor():
    form = NewProfessorForm()
    if form.validate_on_submit():
        str_professor_id = str(form.professor_id.data)
        if not str_professor_id.startswith("5"):
            flash('教师工号必须以数字5开头，请确认后重新添加！')
            return render_template('registrar/registrar_add_professor.html', form=form)

        dummy_professor = Professor.query.filter(Professor.professor_id == form.professor_id.data).first()
        if dummy_professor is not None:
            flash('系统已存在该教师！请检查教师工号输入是否正确！')
            return render_template('registrar/registrar_add_professor.html', form=form)

        professor = Professor(professor_id=form.professor_id.data,
                              password=form.password.data,
                              professor_name=form.professor_name.data,
                              date_of_birth=datetime.datetime.strptime(form.date_of_birth.data, '%Y-%m-%d'),
                              social_security_number=form.social_security_number.data,
                              status=form.status.data,
                              department=form.department.data)
        db.session.add(professor)
        db.session.commit()
        flash('成功添加教师')
        return redirect(url_for('rgs.registrar_maintain_professor_information'))
    return render_template('registrar/registrar_add_professor.html', form=form)


@rgs.route('/registrar-delete-professor', methods=['GET', 'POST'])
@login_required
def registrar_delete_professor():
    form = ProfessorForm()
    if form.validate_on_submit():
        professor = Professor.query.filter(Professor.professor_id == form.professor_id.data).first()
        if professor is None:
            flash('系统不存在该教师')
            return render_template('registrar/registrar_delete_professor.html', form=form)
        # 删除该教师
        db.session.delete(professor)
        db.session.commit()
        # 这里可能要考虑其他表中该教师的信息会不会自动根据key更新，如果会，则不需要额外的操作
        flash('成功删除该教师')
        return redirect(url_for('rgs.registrar_maintain_professor_information'))
    return render_template('registrar/registrar_delete_professor.html', form=form)


@rgs.route('/registrar-update-professor', methods=['GET', 'POST'])
@login_required
def registrar_update_professor():
    form = UpdateProfessorForm()
    if form.validate_on_submit():
        professor = Professor.query.filter(Professor.professor_id == form.professor_id.data).first()
        if professor is None:
            flash('系统不存在该教师')
            return render_template('registrar/registrar_update_professor.html', form=form)
        # 这里用if-else暴力搜索，可能会有一些问题
        if form.password.data != "":
            professor.password = form.password.data
        if form.professor_name.data != "":
            professor.professor_name = form.professor_name.data
        if form.date_of_birth.data != "":
            professor.date_of_birth = datetime.datetime.strptime(form.date_of_birth.data, '%Y-%m-%d')
        if form.social_security_number.data != "":
            professor.social_security_number = form.social_security_number.data
        if form.status.data != "":
            professor.status = form.status.data
        if form.department.data != "":
            professor.department = form.department.data
        db.session.add(professor)
        db.session.commit()
        flash('更新教师信息成功')
        return redirect(url_for('rgs.registrar_maintain_professor_information'))
    return render_template('registrar/registrar_update_professor.html', form=form)


# ---------------------------
# 关闭选课
# ---------------------------


@rgs.route('/registrar-list-course-to-cancel', methods=['GET', 'POST'])
@login_required
def registrar_list_course_to_cancel():
    # 本路由为管理员列出此时此刻选课人数小于3人的课程
    # 等到规定时间，管理员就可以进行操作，但在选课结束之前，也给管理员列出来，管理员能看见，但他不会这个时候去取消
    # 这一点不归系统管，完全是人为操作

    today = datetime.datetime.today()
    thisSemester = str(today.year)
    if int(today.month) < 7:
        thisSemester += "1"
    else:
        thisSemester += "2"
    # 查询数据库，列出人数小于3人的课程
    # select course_id
    # from (select course_id, count(*) as amount
    # from takes
    # where isPrimary = True
    # group by course_id) temp
    # where temp.amount < 3

    dic = dict()
    takes = Takes.query.filter(Takes.isPrimary == True).all()
    for take in takes:
        if take.section_id in dic.keys():
            dic[take.section_id] += 1
        else:
            dic[take.section_id] = 1

    section_ids = [item for item in dic.keys() if dic[item] < 3]
    # 这样只包含了选课人数为1-2人的课程，为零人的课程需要单独考虑

    sections_with_0_registration = []
    all_sections = Section.query.filter(Section.semester == thisSemester).all()
    for section in all_sections:
        if section.section_id not in dic.keys():
            sections_with_0_registration.append(section.section_id)

    section_ids = section_ids + sections_with_0_registration

    # 加入没有老师教的sections
    section_with_0_professor = []
    all_teaches = Teaches.query.all()
    taught_section_ids = [teach.section_id for teach in all_teaches]
    for section in all_sections:
        if section.section_id not in taught_section_ids and section.section_id not in section_ids:
            section_with_0_professor.append(section.section_id)

    section_ids = section_ids + section_with_0_professor

    # if len(section_ids)==0:
    #     flash('len(section_ids)==0')
    # temp = Takes.query(Takes.section_id, func.count(Takes.section_id).label('amount')).filter(
    #     Takes.isPrimary).group_by(
    #     Takes.section_id).all()
    # sections_ids = db.session.query(temp).filter(temp.amount < 3).all()
    sections_with_less_than_3_capacity = Section.query.filter(Section.section_id.in_(section_ids)).all()

    section_list = []
    for section in sections_with_less_than_3_capacity:
        course = Course.query.filter(Course.course_id == section.course_id).first()
        if course is not None:  # 因为有外键设置所以实际上是不会为空的，但考虑到数据库的初始数据是自己加的，所以可能有疏漏
            section_list.append(section)

    # 本路由仅查询数据，交给前端，前端为每门课提供链接到下一个路由，即rgs.registrar_cancel_course
    # 因为下一个路由，即rgs.registrar_cancel_course需要section_id作为参数，所以本路由返回到前端的数据最好是课程实例的列表，必须包含课程id
    return render_template('registrar/registrar_list_course_to_cancel.html',
                           section_list=section_list)


@rgs.route('/registrar-close-registration-view', methods=['GET', 'POST'])
@login_required
def registrar_close_registration_view():
    return render_template('registrar/registrar_close_registration_view.html')


@rgs.route('/registrar-close-registration', methods=['GET', 'POST'])
@login_required
def registrar_close_registration():
    openingobj = Openingconfig.query.first()
    opening = openingobj.value
    if not opening:
        flash('注册已关闭')
    else:
        openingobj.value = False
        db.session.commit()
        flash('关闭注册成功！')
    return redirect(url_for('rgs.registrar_close_registration_view'))


@rgs.route('/registrar-cancel-course/<int:section_id>', methods=['GET', 'POST'])
@login_required
def registrar_cancel_course(section_id):
    # 本路由取消给定section_id的课程，并将新的消息
    section = Section.query.filter(Section.section_id == section_id).first()
    message_body = "课程[" + str(section.course.course_name) + "]因选课人数不足，已被取消！"
    # 这里的提示信息需要改一下直接报上去section_id有点突兀，
    # 可以是指出课程名和课程的学期
    # 添加到input_box中
    # 除此之外，还需将学生的备选课(需查询takes表，检查isPrimary）给他换成实际的课程（将isPrimary置为True）

    today = datetime.datetime.today()
    thisSemester = str(today.year)
    if int(today.month) < 7:
        thisSemester += "1"
    else:
        thisSemester += "2"
    # 步骤
    # 1. 查到选这门课的所有学生 students
    # student_ids = Takes.query(Takes.student_id).filter(Takes.section_id == section_id).all()
    student_takes = Takes.query.filter(Takes.section_id == section_id).all()
    student_ids = [take.student_id for take in student_takes]
    # 2. 查到选这门课的所有老师 professors
    # professor_ids = Teaches.query(Teaches.professor_id).filter(Teaches.course_id == section_id).all()
    professor_teaches = Teaches.query.filter(Teaches.section_id == section_id).all()
    professor_ids = [teach.professor_id for teach in professor_teaches]
    # 3. 添加到input_box

    for student_id in student_ids:
        input_box = Inputbox(user_id=student_id, message=message_body)
        db.session.add(input_box)

    for professor_id in professor_ids:
        input_box = Inputbox(user_id=professor_id, message=message_body)
        db.session.add(input_box)

    db.session.commit()

    # 然后删除该section
    section = Section.query.filter(Section.section_id == section_id).first()
    if section is not None:
        db.session.delete(section)
        db.session.commit()
        flash('您已成功取消该课程！')

    # ----------------------------------------
    # 2020/06/14更新
    # 删除垃圾实例
    take_to_be_deleted = Takes.query.filter(Takes.section_id == section_id).first()
    while take_to_be_deleted is not None:
        db.session.delete(take_to_be_deleted)
        db.session.commit()
        take_to_be_deleted = Takes.query.filter(Takes.section_id == section_id).first()

    teach_to_be_deleted = Teaches.query.filter(Teaches.section_id == section_id).first()
    while teach_to_be_deleted is not None:
        db.session.delete(teach_to_be_deleted)
        db.session.commit()
        teach_to_be_deleted = Teaches.query.filter(Teaches.section_id == section_id).first()

    # ----------------------------------------
    # 处理学生备选课
    for student_id in student_ids:
        takes = Takes.query.filter(Takes.student_id == student_id, Takes.isPrimary == False).all()
        if len(takes) > 0:
            # 该学生有备选课
            for take in takes:
                # 查到选课的人数
                # temp = Takes.query(func.count(Takes.takes_id).label('amount')).filter(
                #     Takes.section_id == take.section_id).filter(
                #     Takes.isPrimary == True).first()

                all_primary_takes = Takes.query.filter(Takes.section_id == take.section_id,
                                                       Takes.isPrimary == True).all()
                primary_takes_list = [primary_take.takes_id for primary_take in all_primary_takes]

                if len(primary_takes_list) < 10:
                    # 如果满足条件则可以选课
                    take.isPrimary = True

            db.session.commit()

        # 到这里，该学生无备选课

    # 本路由无实际界面，仅用于处理数据
    # 处理完成后，返回到rgs.registrar_list_course_to_cancel
    return redirect(url_for('rgs.registrar_list_course_to_cancel'))


# --------------------------------
# 查看学生信息部分，这部分其实没有要求，但为了方便演示和实际需要，补充了这一部分
# --------------------------------
@rgs.route('/registrar-lookup-professor', methods=['GET', 'POST'])
@login_required
def registrar_lookup_professor():
    form = ProfessorForm()

    if form.validate_on_submit():
        professor = Professor.query.filter(Professor.professor_id == form.professor_id.data).first()
        if professor is None:
            flash('查询失败！系统不存在该教师')
            return render_template('registrar/registrar_lookup_professor.html', form=form, information_list=None)

        # 列出信息该教师
        information_list = [['教职工号', professor.professor_id]]
        if professor.professor_name is not None and professor.professor_name != '':
            information_list.append(['教师姓名', professor.professor_name])
        if professor.date_of_birth is not None:
            birthday = professor.date_of_birth
            birthday_str = str(birthday.year) + "年" + str(birthday.month) + "月" + str(birthday.day) + "日"
            information_list.append(['出生日期', birthday_str])
        if professor.department is not None and professor.department != '':
            information_list.append(['学院', professor.department])
        if professor.status is not None and professor.status != '':
            information_list.append(['职称', professor.status])
        if professor.social_security_number is not None and professor.social_security_number != '':
            information_list.append(['身份证号码', professor.social_security_number])

        return render_template('registrar/registrar_lookup_professor.html', form=form,
                               information_list=information_list)
    return render_template('registrar/registrar_lookup_professor.html', form=form, information_list=None)


@rgs.route('/registrar-lookup-student', methods=['GET', 'POST'])
@login_required
def registrar_lookup_student():
    form = StudentForm()

    if form.validate_on_submit():
        student = Student.query.filter(Student.student_id == form.student_id.data).first()
        if student is None:
            flash('查询失败！系统不存在该学生')
            return render_template('registrar/registrar_lookup_student.html', form=form, information_list=None)

        # 列出信息该教师
        information_list = [['学号', student.student_id]]
        if student.student_name is not None and student.student_name != '':
            information_list.append(['教师姓名', student.student_name])
        if student.date_of_birth is not None:
            birthday = student.date_of_birth
            birthday_str = str(birthday.year) + "年" + str(birthday.month) + "月" + str(birthday.day) + "日"
            information_list.append(['出生日期', birthday_str])
        if student.graduation_date is not None:
            grad_date = student.graduation_date
            grad_date_str = str(grad_date.year) + "年" + str(grad_date.month) + "月" + str(grad_date.day) + "日"
            information_list.append(['毕业日期', grad_date_str])
        if student.department is not None and student.department != '':
            information_list.append(['学院', student.department])
        if student.status is not None and student.status != '':
            information_list.append(['学历', student.status])
        if student.social_security_number is not None and student.social_security_number != '':
            information_list.append(['身份证号码', student.social_security_number])

        return render_template('registrar/registrar_lookup_student.html', form=form,
                               information_list=information_list)
    return render_template('registrar/registrar_lookup_student.html', form=form, information_list=None)
